set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getSearchResults' AND type = 'P') DROP PROCEDURE mov_getSearchResults
GO


-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets Search results

-- =============================================
CREATE PROCEDURE [dbo].[mov_getSearchResults] 
@searchtext varchar(50) ="Bello"
AS
BEGIN

    SELECT @searchtext = '%' + RTRIM(@searchtext) + '%'
	select m.movie_id , m.movie_name, (md.movie_dir_fname + ' ' + md.movie_dir_lname)as dir , mg.movie_genre as genre,
    m.movie_image, m.short_desc 
    from movie m JOIN movie_director md 
    on m.movie_dir_id = md.movie_dir_id
    JOIN movie_genre mg 
    ON m.movie_genre_id = mg.movie_genre_id
    where m.movie_name like  @searchtext or	     
          md.movie_dir_fname like @searchtext or
          md.movie_dir_lname like @searchtext or
          mg.movie_genre like @searchtext   
	

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getMoviesByGenre' AND type = 'P')
DROP PROCEDURE mov_getMoviesByGenre
GO




-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets movies for a director 

-- =============================================
CREATE PROCEDURE [dbo].[mov_getMoviesByGenre] 	
@movie_genre_ID int = 5
AS
BEGIN

	--SELECT @searchtext = '%' + RTRIM(@searchtext) + '%'
	select m.movie_id, m.movie_name, (md.movie_dir_fname + ' ' + md.movie_dir_lname)as dir , mg.movie_genre as genre,
    m.movie_image, m.short_desc 
    from movie m JOIN movie_director md 
    on m.movie_dir_id = md.movie_dir_id
    JOIN movie_genre mg 
    ON m.movie_genre_id = mg.movie_genre_id

    where m.movie_genre_id =@movie_genre_ID

--select * from movie
	

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getGenreListing' AND type = 'P') 
DROP PROCEDURE mov_getGenreListing
GO



-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets director listings

-- =============================================
CREATE PROCEDURE [dbo].[mov_getGenreListing] 	

AS
BEGIN

	SELECT movie_genre_ID, movie_genre 
    FROM Movie_genre
    order by movie_genre
	

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_addToQueue' AND type = 'P') DROP PROCEDURE mov_addToQueue
GO




-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure add movies to queue

-- =============================================
CREATE PROCEDURE [dbo].[mov_addToQueue] 
--@cus_id int =1,
@cus_email varchar(50) = 'cbarker.yahoo.com',
@movie_id int =1
AS
BEGIN

--SET IDENTITY_INSERT cus_queue ON

    declare @q_order int 
    declare @cus_id int
    declare @queued int
    declare @rented int

    Declare @quenumber int;
	Set @quenumber = (Select ISNULL(max(que_ID),0) from dbo.cus_queue) + 1
	SELECT @quenumber
    
    SET @cus_id = (Select cus_id from Customer where cus_email= @cus_email )
    Set @q_order = (select ISNULL(max(que_order),0) + 1 from cus_queue where  cus_id =  @cus_id)
    
   -- Prevent dup addition if rented
    SET @rented = (Select count(*) from rental r JOIN cus_queue cq ON r.que_id = cq.que_id
	              where cq.cus_id = @cus_id and rent_receive_date is null and cq.movie_id = @movie_id )

   -- Prevent dup addition if queued
     SET @queued = (SELECT count(*) from cus_queue where cus_id = @cus_id and movie_id = @movie_id)

    IF @rented < 1 AND 	@queued  < 1  
	INSERT INTO dbo.cus_queue
	VaLUES(@quenumber,@q_order, @cus_id ,@movie_id)
   
END

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Modified:    Jeff Green
-- Create date: 6/7/2011
-- Description:	The procedure returns all of the movies in a customer's queue for human-readable display

-- =============================================

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getCustomerQueueForDisplay' AND type = 'P') DROP PROCEDURE mov_getCustomerQueueForDisplay
GO

CREATE PROCEDURE [dbo].[mov_getCustomerQueueForDisplay]
	@email varchar(40)
AS
BEGIN
	SELECT m.movie_name as Movie, (md.movie_dir_fname + ' ' +  md.movie_dir_lname) as Director , mg.movie_genre as Genre
    --,m.movie_image
	--, m.short_desc as Description
	FROM [dbo].[customer] c
	INNER JOIN [dbo].[cus_queue] q ON c.cus_id = q.cus_id
	INNER JOIN [dbo].[movie] m ON q.movie_id = m.movie_id
	JOIN [dbo].[movie_director] md ON m.movie_dir_id= md.movie_dir_id
	JOIN [dbo].[movie_genre] mg ON m.movie_genre_id = mg.movie_genre_id
	WHERE c.cus_email = @email
	ORDER BY q.que_order

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getMoviesByDirector' AND type = 'P') 
DROP PROCEDURE mov_getMoviesByDirector
GO



-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets movies for a director 

-- =============================================
CREATE PROCEDURE [dbo].[mov_getMoviesByDirector] 	
@movie_dir_ID int = 5
AS
BEGIN

	--SELECT @searchtext = '%' + RTRIM(@searchtext) + '%'
	select m.movie_id, m.movie_name, (md.movie_dir_fname + ' ' + md.movie_dir_lname)as dir , mg.movie_genre as genre,
    m.movie_image, m.short_desc 
    from movie m JOIN movie_director md 
    on m.movie_dir_id = md.movie_dir_id
    JOIN movie_genre mg 
    ON m.movie_genre_id = mg.movie_genre_id

    where m.movie_dir_id =@movie_dir_ID

--select * from movie
	

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getDirectorListing' AND type = 'P') DROP PROCEDURE mov_getDirectorListing
GO



-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets director listings

-- =============================================
CREATE PROCEDURE [dbo].[mov_getDirectorListing] 	

AS
BEGIN

	SELECT movie_dir_ID, movie_dir_fname + ' ' + movie_dir_lname as movie_dir_name, movie_dir_fname,movie_dir_lname 
    FROM Movie_director
    order by movie_dir_fname
	

END


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getNewListings' AND type = 'P') DROP PROCEDURE mov_getNewListings
GO


-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure gets new listings

-- =============================================
CREATE PROCEDURE [dbo].[mov_getNewListings] 	

AS
BEGIN

	select m.movie_id ,m.movie_name, (md.movie_dir_fname + ' ' +  md.movie_dir_lname)as dir , mg.movie_genre as genre,
    m.movie_image, m.short_desc 
    from movie m JOIN movie_director md 
    on m.movie_dir_id = md.movie_dir_id
    JOIN movie_genre mg 
    ON m.movie_genre_id = mg.movie_genre_id

    where movie_rel_date >= getdate()-30 
	

END



SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Modified:    Jeff Green
-- Create date: 6/7/2011
-- Description:	The procedure returns all of the movies in a customer's queue

-- =============================================

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_getCustomerQueue' AND type = 'P') DROP PROCEDURE mov_getCustomerQueue
GO

CREATE PROCEDURE [dbo].[mov_getCustomerQueue]
	@email varchar(40)
AS
BEGIN
	SELECT q.*
	FROM [dbo].[customer] c
	INNER JOIN [dbo].[cus_queue] q ON c.cus_id = q.cus_id
	WHERE c.cus_email = @email
	ORDER BY q.que_order
END



GO


/****** Object:  StoredProcedure [dbo].[mov_getRentalsforCustomer]    Script Date: 06/12/2011 14:24:15 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mov_getRentalsforCustomer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mov_getRentalsforCustomer]
GO



/****** Object:  StoredProcedure [dbo].[mov_getRentalsforCustomer]    Script Date: 06/12/2011 14:24:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Modified:    Pritam Bhat
-- Create date: 6/04/2011
-- Description:	This procedure returns all the rental details
-- Modified Date: 
-- Modified   : 
-- Modified By: 

-- =============================================
CREATE PROCEDURE [dbo].[mov_getRentalsforCustomer] 	
(
	@email varchar(40)
)
AS
BEGIN
	
	SET NOCOUNT ON;


select r.rent_track_num as 'Rental Track', m.movie_name as 'Movie Name'
from customer c join cus_queue q 
on c.cus_id = q.cus_id join rental r
on r.que_id = q.que_id JOIN movie m
on q.movie_id =m.movie_id
where 
rent_receive_date is null AND
c.cus_email = @email

END




GO

















